const { Question,PsychologyQuestionnaire,QuestionnairePart,PsychologyReport,QuestionOption } = require('../db')
const { User, QuestionScoreRange } = require('../db'); 
const xlsx = require('xlsx');
const { Op } = require('sequelize');
const path = require('path');
const multer = require('multer');

// 创建心理问卷
exports.createQuestionnaire = async (req, res) => {
    const { title, scoring } = req.body;
    try {
        const questionnaire = await PsychologyQuestionnaire.create({
            title,
            scoring
        });
        res.status(201).json({
            status: 201,
            message: '心理问卷创建成功',
            data: questionnaire
        });
    } catch (error) {
        console.error(error);
        res.status(500).json({
            status: 500,
            message: '创建心理问卷失败'
        });
    }
};

// 获取心理问卷列表
exports.getQuestionnaireList = async (req, res) => {
    try {
        const questionnaires = await PsychologyQuestionnaire.findAll({
            order: [['id', 'DESC']]
        });
        res.status(200).json({
            status: 200,
            message: '获取心理问卷列表成功',
            data: questionnaires
        });
    } catch (error) {
        console.error(error);
        res.status(500).json({
            status: 500,
            message: '获取心理问卷列表失败'
        });
    }
};

// 更新心理问卷
exports.updateQuestionnaire = async (req, res) => {
    const { id } = req.body;
    const { title, scoring } = req.body;
    try {
        const [updated] = await PsychologyQuestionnaire.update({
            title,
            scoring
        }, {
            where: { id }
        });
        if (updated) {
            res.status(200).json({
                status: 200,
                message: '心理问卷更新成功'
            });
        } else {
            res.status(404).json({
                status: 404,
                message: '未找到指定的心理问卷'
            });
        }
    } catch (error) {
        console.error(error);
        res.status(500).json({
            status: 500,
            message: '更新心理问卷失败'
        });
    }
};

// 删除心理问卷
exports.deleteQuestionnaire = async (req, res) => {
    const { id } = req.body;
    try {
        // 先删除关联的分数段定义
        await QuestionScoreRange.destroy({
            where: { questionnaire_id: id }
        });

        // 删除关联的心理报告
        await PsychologyReport.destroy({
            where: { questionnaire_id: id }
        });

        // 删除关联的题目关系
        await QuestionnairePart.destroy({
            where: { questionnaire_id: id }
        });

        // 删除问卷
        const deleted = await PsychologyQuestionnaire.destroy({
            where: { id }
        });

        if (deleted) {
            res.status(200).json({
                status: 200,
                message: '心理问卷删除成功'
            });
        } else {
            res.status(404).json({
                status: 404,
                message: '未找到指定的心理问卷'
            });
        }
    } catch (error) {
        console.error('删除心理问卷失败:', error);
        res.status(500).json({
            status: 500,
            message: '删除心理问卷失败',
            error: error.message
        });
    }
};

// 添加题目
exports.addQuestion = async (req, res) => {
    const { question_type, question_text } = req.body;
    try {
        const question = await Question.create({
            question_type,
            question_text
        });
        res.status(201).json({
            status: 201,
            message: '题目添加成功',
            data: question
        });
    } catch (error) {
        console.error(error);
        res.status(500).json({
            status: 500,
            message: '添加题目失败'
        });
    }
};
// 获取指定问卷的题目列表
exports.getQuestionList = async (req, res) => {
    const { questionnaireId } = req.query; // 从查询参数中获取问卷 ID

    if (!questionnaireId) {
        return res.status(400).json({
            status: 400,
            message: '缺少问卷 ID 参数'
        });
    }

    try {
        // 查询 questionnaire_parts 表，找出与指定问卷 ID 关联的所有记录
        const questionnaireParts = await QuestionnairePart.findAll({
            where: { questionnaire_id: questionnaireId },
            include: [
                {
                    model: Question,
                    attributes: ['id', 'question_type', 'question_text'],
                    include: [
                        {
                            model: QuestionOption,
                            attributes: ['id', 'option_text', 'option_score']
                        }
                    ]
                }
            ]
        });

        // 提取出题目列表
        const questions = questionnaireParts.map(part => {
            const question = part.Question;
            question.options = question.QuestionOptions || [];
            return question;
        });

        res.status(200).json({
            status: 200,
            message: '获取指定问卷的题目列表成功',
            data: questions
        });
    } catch (error) {
        console.error(error);
        res.status(500).json({
            status: 500,
            message: '获取指定问卷的题目列表失败'
        });
    }
};

// 更新题目
exports.updateQuestion = async (req, res) => {
    const { id } = req.body;
    const { question_type, question_text } = req.body;
    try {
        const [updated] = await Question.update({
            question_type,
            question_text
        }, {
            where: { id }
        });
        if (updated) {
            res.status(200).json({
                status: 200,
                message: '题目更新成功'
            });
        } else {
            res.status(404).json({
                status: 404,
                message: '未找到指定的题目'
            });
        }
    } catch (error) {
        console.error(error);
        res.status(500).json({
            status: 500,
            message: '更新题目失败'
        });
    }
};

// 删除题目
exports.deleteQuestion = async (req, res) => {
    const { id } = req.body;
    try {
        // 先删除关联的问卷部分关系
        await QuestionnairePart.destroy({
            where: { question_id: id }
        });

        // 删除题目的选项
        await QuestionOption.destroy({
            where: { question_id: id }
        });

        // 删除题目
        const deleted = await Question.destroy({
            where: { id }
        });

        if (deleted) {
            res.status(200).json({
                status: 200,
                message: '题目删除成功'
            });
        } else {
            res.status(404).json({
                status: 404,
                message: '未找到指定的题目'
            });
        }
    } catch (error) {
        console.error('删除题目失败:', error);
        res.status(500).json({
            status: 500,
            message: '删除题目失败',
            error: error.message
        });
    }
};

// 添加题目
exports.addQuestionToQuestionnaire = async (req, res) => {
    const { question_type, question_text, option_text, option_score } = req.body;
    try {
        const question = await Question.create({
            question_type,
            question_text
        });

        // 如果前端发送了选项内容和分值，则创建对应的题目选项记录
        if (option_text && option_score) {
            await QuestionOption.create({
                question_id: question.id,
                option_text,
                option_score
            });
        }

        res.status(201).json({
            status: 201,
            message: '题目添加成功',
            data: question
        });
    } catch (error) {
        console.error(error);
        res.status(500).json({
            status: 500,
            message: '添加题目失败'
        });
    }
};

// 从问卷中移除题目
exports.removeQuestionFromQuestionnaire = async (req, res) => {
    const { questionnaire_id, question_id } = req.body;
    try {
        const deleted = await QuestionnairePart.destroy({
            where: {
                questionnaire_id,
                question_id
            }
        });
        if (deleted) {
            res.status(200).json({
                status: 200,
                message: '从问卷中移除题目成功'
            });
        } else {
            res.status(404).json({
                status: 404,
                message: '未找到指定的问卷题目关系'
            });
        }
    } catch (error) {
        console.error(error);
        res.status(500).json({
            status: 500,
            message: '从问卷中移除题目失败'
        });
    }
};

// 获取问卷的所有题目
exports.getQuestionnaireQuestions = async (req, res) => {
    const { questionnaire_id } = req.body;
    try {
        const parts = await QuestionnairePart.findAll({
            where: { questionnaire_id },
            include: [{
                model: Question,
                as: 'question', // 指定关联别名
                attributes: ['id', 'question_type', 'question_text'],
                include: [{
                    model: QuestionOption,
                    as: 'options', // 指定关联别名
                    attributes: ['option_text', 'option_score']
                }]
            }],
            order: [['id', 'ASC']]
        });

        const questions = parts.map(part => {
            const question = part.question; // 使用别名访问关联的问题
            question.options = question.options || [];
            return question;
        });

        res.status(200).json({
            status: 200,
            message: '获取问卷题目成功',
            data: questions
        });
    } catch (error) {
        console.error(error);
        res.status(500).json({
            status: 500,
            message: '获取问卷题目失败'
        });
    }
};
// 批量导入心理问卷题目
exports.uploadXlsxQuestions = async (req, res) => {
    try {
        if (!req.file) {
            return res.status(400).json({
                status: 400,
                message: '请上传Excel文件'
            });
        }

        const workbook = xlsx.readFile(req.file.path);
        const sheetName = workbook.SheetNames[0];
        const data = xlsx.utils.sheet_to_json(workbook.Sheets[sheetName]);

        const importedQuestions = [];
        const skippedQuestions = [];
        const questionnaireId = req.body.questionnaire_id;

        for (const row of data) {
            try {
                // 校验必填字段
                if (!row.question_text || !row.question_type) {
                    skippedQuestions.push({
                        row: row,
                        reason: '缺少必填字段(question_text或question_type)'
                    });
                    continue;
                }

                // 创建题目
                const question = await Question.create({
                    question_type: row.question_type,
                    question_text: row.question_text
                });

                // 如果有选项，创建选项
                if (row.options && Array.isArray(row.options)) {
                    for (const option of row.options) {
                        if (option.text && option.score !== undefined) {
                            await QuestionOption.create({
                                question_id: question.id,
                                option_text: option.text,
                                option_score: option.score
                            });
                        }
                    }
                }

                // 如果提供了questionnaire_id，则将题目添加到问卷中
                if (questionnaireId) {
                    const questionnaire = await PsychologyQuestionnaire.findByPk(questionnaireId);
                    if (questionnaire) {
                        await QuestionnairePart.create({
                            questionnaire_id: questionnaireId,
                            question_id: question.id
                        });
                    } else {
                        skippedQuestions.push({
                            question: question.question_text,
                            reason: '指定的问卷不存在'
                        });
                    }
                }

                importedQuestions.push(question);
            } catch (error) {
                console.error('导入题目失败:', error);
                skippedQuestions.push({
                    row: row,
                    reason: error.message
                });
            }
        }

        res.status(200).json({
            status: 200,
            message: '问卷题目导入完成',
            data: {
                imported_count: importedQuestions.length,
                skipped_count: skippedQuestions.length,
                imported_questions: importedQuestions.map(q => q.id),
                skipped_questions: skippedQuestions
            }
        });
    } catch (error) {
        console.error('导入失败:', error);
        res.status(500).json({
            status: 500,
            message: '导入问卷题目失败',
            error: error.message
        });
    }
};

// 自定义错误类，用于更明确地处理不同类型的错误
class CustomError extends Error {
    constructor(message, statusCode) {
        super(message);
        this.statusCode = statusCode;
    }
}

// 封装检查问卷和用户是否存在的函数
const checkQuestionnaireAndUser = async (questionnaire_id, user_id) => {
    const questionnaire = await PsychologyQuestionnaire.findByPk(questionnaire_id);
    if (!questionnaire) {
        throw new CustomError(`问卷不存在，ID: ${questionnaire_id}`, 404);
    }

    const user = await User.findByPk(user_id);
    if (!user) {
        throw new CustomError(`用户不存在，ID: ${user_id}`, 404);
    }

    return { questionnaire, user };
};

// 封装计算总分的函数
const calculateTotalScore = async (answers) => {
    let totalScore = 0;
    
    // 按问题ID分组答案，以处理多选题
    const answersByQuestion = {};
    for (const answer of answers) {
        const { question_id } = answer;
        if (!answersByQuestion[question_id]) {
            answersByQuestion[question_id] = [];
        }
        answersByQuestion[question_id].push(answer);
    }

    // 计算每个问题的得分
    for (const [questionId, questionAnswers] of Object.entries(answersByQuestion)) {
        let questionScore = 0;
        
        // 累加所有选中选项的分数
        for (const answer of questionAnswers) {
            const selectedOption = await QuestionOption.findOne({
                where: { 
                    question_id: questionId,
                    option_text: answer.user_answer 
                }
            });
            const optionScore = selectedOption ? selectedOption.option_score : 0;
            questionScore += optionScore;
        }

        totalScore += questionScore;
    }

    return totalScore;
};

// 用户答题功能
exports.submitQuestionnaire = async (req, res) => {
    const { questionnaire_id, user_id, answers } = req.body;
    try {
        await checkQuestionnaireAndUser(questionnaire_id, user_id);

        console.log(`用户提交问卷：用户ID=${user_id}, 问卷ID=${questionnaire_id}, 答案数量=${answers.length}`);

        // 计算总分
        const totalScore = await calculateTotalScore(answers);

        // 查找或创建报告记录
        const [report, created] = await PsychologyReport.findOrCreate({
            where: {
                user_id,
                questionnaire_id
            },
            defaults: {
                user_id,
                questionnaire_id,
                user_answers: answers,
                total_score: totalScore,
                content: '问卷已提交，正在生成报告...'
            }
        });

        // 如果是更新已有记录
        if (!created) {
            await report.update({
                user_answers: answers,
                total_score: totalScore
            });
        }

        console.log(`问卷提交成功，总分：${totalScore}，开始批改和生成报告`);

        // 自动进行批改
        try {
            // 1. 获取问卷的所有分数段
            const scoreRanges = await QuestionScoreRange.findAll({
                where: { questionnaire_id },
                order: [['range_name', 'ASC'], ['start_question_id', 'ASC']]
            });
            
            if (scoreRanges.length === 0) {
                console.log(`警告: 未找到问卷ID ${questionnaire_id} 的分数段定义，无法生成详细分析`);
            }

            // 2. 根据分数段生成分析
            const rangeGroups = {};
            scoreRanges.forEach(range => {
                if (!rangeGroups[range.range_name]) {
                    rangeGroups[range.range_name] = [];
                }
                rangeGroups[range.range_name].push(range);
            });
            
            // 3. 计算每个区域的得分和分析
            const rangeResults = {};
            
            for (const [rangeName, ranges] of Object.entries(rangeGroups)) {
                // 获取此分组的题目范围
                const minQuestionId = Math.min(...ranges.map(r => r.start_question_id));
                const maxQuestionId = Math.max(...ranges.map(r => r.end_question_id));
                
                // 计算此范围内的得分
                let rangeScore = 0;
                const rangeQuestions = [];
                
                for (const answer of answers) {
                    const questionId = parseInt(answer.question_id);
                    if (questionId >= minQuestionId && questionId <= maxQuestionId) {
                        // 查找选项分数
                        const option = await QuestionOption.findOne({
                            where: { 
                                question_id: questionId, 
                                option_text: answer.user_answer 
                            }
                        });
                        
                        const score = option ? option.option_score : 0;
                        rangeScore += score;
                        
                        rangeQuestions.push({
                            question_id: questionId,
                            user_answer: answer.user_answer,
                            option_score: score
                        });
                    }
                }
                
                // 找到对应的心理状态和建议
                const matchedRange = ranges.find(r => 
                    rangeScore >= r.score_min && rangeScore <= r.score_max
                );
                
                // 添加到结果
                rangeResults[rangeName] = {
                    range_name: rangeName,
                    start_question_id: minQuestionId,
                    end_question_id: maxQuestionId,
                    total_score: rangeScore,
                    mental_status: matchedRange ? matchedRange.mental_status : '未知',
                    advice: matchedRange ? matchedRange.advice : '暂无建议',
                    questions: rangeQuestions
                };
            }

            // 4. 查找总分对应的心理状态和建议
            let mentalStatus = null;
            let advice = null;
            
            const overallScoreRange = await QuestionScoreRange.findOne({
                where: {
                    questionnaire_id,
                    score_min: { [Op.lte]: totalScore },
                    score_max: { [Op.gte]: totalScore }
                }
            });
            
            if (overallScoreRange) {
                mentalStatus = overallScoreRange.mental_status;
                advice = overallScoreRange.advice;
            }

            // 5. 生成报告内容
            const [questionnaire, user] = await Promise.all([
                PsychologyQuestionnaire.findByPk(questionnaire_id),
                User.findByPk(user_id)
            ]);

            let reportContent = `心理评估报告\n\n`;
            reportContent += `用户: ${user?.name || user?.username || '未知用户'}\n`;
            reportContent += `问卷: ${questionnaire?.title || '未知问卷'}\n`;
            reportContent += `评估时间: ${new Date().toLocaleString()}\n\n`;
            reportContent += `总分: ${totalScore}\n`;
            reportContent += `心理状态: ${mentalStatus || '未确定'}\n`;
            reportContent += `建议: ${advice || '暂无建议'}\n\n`;
            reportContent += `——————— 各维度分析 ———————\n\n`;
            
            // 添加各区域分析
            for (const [rangeName, rangeData] of Object.entries(rangeResults)) {
                reportContent += `【${rangeName}】(题目${rangeData.start_question_id}-${rangeData.end_question_id}):\n`;
                reportContent += `得分: ${rangeData.total_score}\n`;
                reportContent += `状态: ${rangeData.mental_status}\n`;
                reportContent += `建议: ${rangeData.advice}\n\n`;
                
                // 添加答题详情
                if (rangeData.questions && rangeData.questions.length > 0) {
                    reportContent += `答题详情:\n`;
                    for (const q of rangeData.questions) {
                        reportContent += `  问题ID ${q.question_id}: 答案 "${q.user_answer}", 得分 ${q.option_score}\n`;
                    }
                    reportContent += `\n`;
                }
            }

            // 6. 更新报告
            await report.update({
                total_score: totalScore,
                content: reportContent,
                mental_status: mentalStatus,
                advice: advice,
                range_results: rangeResults,
                detailed_analysis: JSON.stringify(rangeResults)
            });
            
            console.log(`报告自动批改和生成完成：ID=${report.id}`);
            
        } catch (error) {
            console.error('自动批改生成报告失败:', error);
            // 继续处理，不要因为批改错误影响提交问卷的响应
        }

        // 返回成功响应
        res.status(201).json({
            status: 201,
            message: '问卷提交成功',
            data: {
                report_id: report.id,
                total_score: totalScore
            }
        });
    } catch (error) {
        console.error('问卷提交失败:', error);
        res.status(error.statusCode || 500).json({
            status: error.statusCode || 500,
            message: error.message || '问卷提交失败'
        });
    }
};

// 问卷批改功能
exports.gradeQuestionnaire = async (req, res) => {
    const { questionnaire_id, user_id } = req.body;
    try {
        await checkQuestionnaireAndUser(questionnaire_id, user_id);

        const report = await PsychologyReport.findOne({
            where: { user_id, questionnaire_id }
        });

        if (!report) {
            throw new CustomError(`未找到用户的问卷报告，用户ID: ${user_id}, 问卷ID: ${questionnaire_id}`, 404);
        }

        const answers = report.user_answers;
        const scoreRanges = await QuestionScoreRange.findAll({
            where: { questionnaire_id },
            order: [['start_question_id', 'ASC']]
        });

        let totalScore = 0;
        const rangeResults = [];

        for (const range of scoreRanges) {
            let rangeScore = 0;
            for (const answer of answers) {
                if (answer.question_id >= range.start_question_id && answer.question_id <= range.end_question_id) {
                    const selectedOption = await QuestionOption.findOne({
                        where: { question_id: answer.question_id, option_text: answer.user_answer }
                    });
                    const optionScore = selectedOption? selectedOption.option_score : 0;
                    rangeScore += optionScore;
                }
            }

            const matchedRange = scoreRanges.find(r => r.start_question_id === range.start_question_id && r.end_question_id === range.end_question_id && r.score_min <= rangeScore && r.score_max >= rangeScore);
            const mentalStatus = matchedRange? matchedRange.mental_status : '未知';
            const advice = matchedRange? matchedRange.advice : '暂无建议';

            rangeResults.push({
                start_question_id: range.start_question_id,
                end_question_id: range.end_question_id,
                total_score: rangeScore,
                mental_status: mentalStatus,
                advice: advice
            });

            totalScore += rangeScore;
        }

        await report.update({
            total_score: totalScore,
            range_results: rangeResults,
            mental_status: rangeResults.length > 0? rangeResults[0].mental_status : '未知',
            advice: rangeResults.length > 0? rangeResults[0].advice : '暂无建议'
        });

        res.status(200).json({
            status: 200,
            message: '问卷批改成功',
            data: {
                total_score: report.total_score,
                range_results: rangeResults,
                mental_status: report.mental_status,
                advice: report.advice
            }
        });
    } catch (error) {
        console.error('问卷批改失败:', error);
        res.status(error.statusCode || 500).json({
            status: error.statusCode || 500,
            message: error.message
        });
    }
};

// 生成心理报告功能
exports.generatePsychologyReport = async (req, res) => {
    const { questionnaire_id, user_id } = req.body;
    try {
        console.log(`开始生成心理报告，问卷ID: ${questionnaire_id}, 用户ID: ${user_id}`);
        
        await checkQuestionnaireAndUser(questionnaire_id, user_id);

        // 查找或创建报告记录
        let report = await PsychologyReport.findOne({
            where: { user_id, questionnaire_id }
        });

        if (!report) {
            console.log('未找到报告记录，创建新报告');
            report = await PsychologyReport.create({
                user_id,
                questionnaire_id,
                content: '正在生成报告...'
            });
        }
        
        // 如果report.user_answers为空，无法进行分析
        if (!report.user_answers || !Array.isArray(report.user_answers) || report.user_answers.length === 0) {
            throw new CustomError('报告缺少答题数据，无法生成分析', 400);
        }

        // 获取问卷和用户信息
        const [questionnaire, user] = await Promise.all([
            PsychologyQuestionnaire.findByPk(questionnaire_id),
            User.findByPk(user_id)
        ]);
        
        if (!questionnaire || !user) {
            throw new CustomError('未找到问卷或用户信息', 404);
        }

        // 计算总分
        const totalScore = await calculateTotalScore(report.user_answers);
        console.log(`计算总分: ${totalScore}`);
        
        // 获取问卷的所有分数段
        const scoreRanges = await QuestionScoreRange.findAll({
            where: { questionnaire_id },
            order: [['range_name', 'ASC'], ['start_question_id', 'ASC']]
        });
        
        // 没有分数段定义时，直接生成简单报告
        if (scoreRanges.length === 0) {
            console.log(`警告: 未找到问卷ID ${questionnaire_id} 的分数段定义，生成简单报告`);
            
            const simpleContent = `心理评估报告\n\n` +
                `用户: ${user.name || user.username || '未知用户'}\n` +
                `问卷: ${questionnaire.title}\n` +
                `评估时间: ${new Date().toLocaleString()}\n\n` +
                `总分: ${totalScore}\n\n` +
                `该问卷暂无详细分析配置。`;
            
            await report.update({
                total_score: totalScore,
                content: simpleContent
            });
            
            return res.status(200).json({
                status: 200,
                message: '心理报告生成成功（简单版）',
                data: {
                    id: report.id,
                    questionnaire_id: report.questionnaire_id,
                    questionnaire_title: questionnaire.title,
                    user_id: report.user_id,
                    user_name: user.name || user.username || '未知用户',
                    total_score: totalScore,
                    content: simpleContent
                }
            });
        }

        // 按区域名称分组
        const rangeGroups = {};
        scoreRanges.forEach(range => {
            if (!rangeGroups[range.range_name]) {
                rangeGroups[range.range_name] = [];
            }
            rangeGroups[range.range_name].push(range);
        });
        
        // 计算每个分组区域的分数和结果
        const rangeResults = {};
        
        for (const [rangeName, ranges] of Object.entries(rangeGroups)) {
            // 获取此分组的题目范围
            const minQuestionId = Math.min(...ranges.map(r => r.start_question_id));
            const maxQuestionId = Math.max(...ranges.map(r => r.end_question_id));
            
            // 计算此范围内的得分
            let rangeScore = 0;
            const rangeQuestions = [];
            
            for (const answer of report.user_answers) {
                const questionId = parseInt(answer.question_id);
                if (questionId >= minQuestionId && questionId <= maxQuestionId) {
                    // 查找相应选项和分数
                    try {
                        const option = await QuestionOption.findOne({
                            where: { 
                                question_id: questionId, 
                                option_text: answer.user_answer 
                            }
                        });
                        
                        const score = option ? option.option_score : 0;
                        rangeScore += score;
                        
                        rangeQuestions.push({
                            question_id: questionId,
                            user_answer: answer.user_answer,
                            option_score: score
                        });
                    } catch (err) {
                        console.error(`处理问题ID=${questionId}时出错:`, err);
                        // 继续处理其他题目
                    }
                }
            }
            
            // 找到对应分数段的心理状态和建议
            const matchedRange = ranges.find(r => 
                rangeScore >= r.score_min && rangeScore <= r.score_max
            );
            
            // 添加到结果中
            rangeResults[rangeName] = {
                range_name: rangeName,
                start_question_id: minQuestionId,
                end_question_id: maxQuestionId,
                total_score: rangeScore,
                mental_status: matchedRange ? matchedRange.mental_status : '未知',
                advice: matchedRange ? matchedRange.advice : '暂无建议',
                questions: rangeQuestions
            };
        }
        
        // 查找总分对应的心理状态和建议
        let mentalStatus = '未知';
        let advice = '暂无建议';
        
        const overallScoreRange = await QuestionScoreRange.findOne({
            where: {
                questionnaire_id,
                score_min: { [Op.lte]: totalScore },
                score_max: { [Op.gte]: totalScore }
            }
        });
        
        if (overallScoreRange) {
            mentalStatus = overallScoreRange.mental_status;
            advice = overallScoreRange.advice;
        }
        
        // 生成报告内容
        let reportContent = `心理评估报告\n\n`;
        reportContent += `用户: ${user.name || user.username || '未知用户'}\n`;
        reportContent += `问卷: ${questionnaire.title}\n`;
        reportContent += `评估时间: ${new Date().toLocaleString()}\n\n`;
        reportContent += `总分: ${totalScore}\n`;
        reportContent += `心理状态: ${mentalStatus}\n`;
        reportContent += `建议: ${advice}\n\n`;
        reportContent += `——————— 各维度分析 ———————\n\n`;
        
        // 添加各区域分析
        if (Object.keys(rangeResults).length > 0) {
            for (const [rangeName, rangeData] of Object.entries(rangeResults)) {
                reportContent += `【${rangeName}】(题目${rangeData.start_question_id}-${rangeData.end_question_id}):\n`;
                reportContent += `得分: ${rangeData.total_score}\n`;
                reportContent += `状态: ${rangeData.mental_status}\n`;
                reportContent += `建议: ${rangeData.advice}\n\n`;
                
                // 添加答题详情
                if (rangeData.questions && rangeData.questions.length > 0) {
                    reportContent += `答题详情:\n`;
                    for (const q of rangeData.questions) {
                        reportContent += `  问题ID ${q.question_id}: 答案 "${q.user_answer}", 得分 ${q.option_score}\n`;
                    }
                    reportContent += `\n`;
                }
            }
        } else {
            reportContent += `暂无详细分析数据\n`;
        }
        
        // 更新报告
        await report.update({
            total_score: totalScore,
            content: reportContent,
            mental_status: mentalStatus,
            advice: advice,
            range_results: rangeResults,
            detailed_analysis: JSON.stringify(rangeResults)
        });
        
        console.log('生成报告完成');
        
        res.status(200).json({
            status: 200,
            message: '心理报告生成成功',
            data: {
                id: report.id,
                questionnaire_id: report.questionnaire_id,
                questionnaire_title: questionnaire.title,
                user_id: report.user_id,
                user_name: user.name || user.username || '未知用户',
                total_score: totalScore,
                mental_status: mentalStatus,
                advice: advice,
                content: reportContent,
                range_results: rangeResults
            }
        });
    } catch (error) {
        console.error('生成心理报告失败:', error);
        res.status(error.statusCode || 500).json({
            status: error.statusCode || 500,
            message: error.message || '生成心理报告失败',
            stack: process.env.NODE_ENV === 'development' ? error.stack : undefined
        });
    }
};
// 获取用户的心理报告
exports.getUserPsychologyReports = async (req, res) => {
    const { user_id } = req.body;
    try {
        const reports = await PsychologyReport.findAll({
            where: { user_id },
            order: [['id', 'DESC']],
            include: [
                {
                    model: PsychologyQuestionnaire,
                    as: 'questionnaire',
                    attributes: ['title']
                },
                {
                    model: User,
                    as: 'user',
                    attributes: ['name']
                }
            ]
        });

        const reportsWithDetails = await Promise.all(reports.map(async report => {
            const scoreRange = await QuestionScoreRange.findOne({
                where: {
                    score_min: { [Op.lte]: report.total_score },
                    score_max: { [Op.gte]: report.total_score }
                }
            });
            
            const mental_status = scoreRange ? scoreRange.mental_status : '未明确';
            const advice = scoreRange ? scoreRange.advice : '暂无建议';

            const content = `心理评估报告\n\n` +
                `用户ID: ${user_id}\n` +
                `问卷ID: ${report.questionnaire_id}\n` +
                `评估总分: ${report.total_score}\n` +
                `心理状态: ${mental_status}\n\n` +
                `详细分析:\n` +
                (report.content || '') + `\n心理状态:\n${mental_status}`+
                `\n专业建议:\n${advice}`;

            return {
                ...report.toJSON(),
                content,
                mental_status,
                advice
            };
        }));

        res.status(200).json({
            status: 200,
            message: '获取用户心理报告成功',
            data: reportsWithDetails
        });
    } catch (error) {
        console.error(error);
        res.status(500).json({
            status: 500,
            message: '获取用户心理报告失败'
        });
    }
};
// 获取用户对应问卷的心理报告详情接口
exports.getPsychologyReportDetails = async (req, res) => {
    const { id } = req.body;
    try {
        // 查找心理报告
        const report = await PsychologyReport.findOne({
            where: { id },
            include: [
                {
                    model: PsychologyQuestionnaire,
                    as: 'questionnaire',
                    attributes: ['title', 'ps_introduction']
                },
                {
                    model: User,
                    as: 'user',
                    attributes: ['name']
                }
            ]
        });

        if (!report) {
            throw new Error(`未找到ID为 ${id} 的问卷报告`);
        }

        // 如果用户名为null，尝试直接查询用户
        let userName = '未知用户';
        if (!report.user?.name && report.user_id) {
            const user = await User.findByPk(report.user_id);
            if (user) {
                userName = user.name || user.username || '未知用户';
            }
        } else if (report.user?.name) {
            userName = report.user.name;
        }
        
        // 如果缺少range_results，尝试生成一些基本分析
        let mentalStatus = report.mental_status;
        let advice = report.advice;
        
        if (!report.range_results || 
            (Array.isArray(report.range_results) && report.range_results.length === 0) ||
            (typeof report.range_results === 'object' && Object.keys(report.range_results).length === 0)) {
            
            console.log(`ID为${id}的报告没有range_results数据，尝试生成基本分析`);
            
            // 尝试获取此分数对应的心理状态和建议
            if (report.total_score !== null && report.total_score !== undefined) {
                try {
                    const scoreRange = await QuestionScoreRange.findOne({
                        where: {
                            questionnaire_id: report.questionnaire_id,
                            score_min: { [Op.lte]: report.total_score },
                            score_max: { [Op.gte]: report.total_score }
                        }
                    });
                    
                    if (scoreRange) {
                        mentalStatus = scoreRange.mental_status;
                        advice = scoreRange.advice;
                        
                        // 如果报告本身没有mental_status和advice，更新它
                        if (!report.mental_status || !report.advice) {
                            await report.update({
                                mental_status: mentalStatus,
                                advice: advice
                            });
                        }
                    }
                } catch (err) {
                    console.error('尝试获取分数范围数据时出错:', err);
                }
            }
        }

        let reportContent = `心理评估报告\n\n`;
        reportContent += `用户: ${userName}\n`;
        reportContent += `问卷: ${report.questionnaire?.title || '未知问卷'}\n`;
        reportContent += `评估时间: ${new Date().toLocaleString()}\n\n`;
        reportContent += `综合评估结果:\n`;
        reportContent += `总分: ${report.total_score || '未评分'}\n`;
        reportContent += `心理状态: ${mentalStatus || '未知'}\n`;
        reportContent += `建议: ${advice}\n\n`;
        reportContent += `详细分析:\n`;

        const rangeResults = report.range_results || [];
        
        // 检查rangeResults是否为数组并且有内容
        if (Array.isArray(rangeResults) && rangeResults.length > 0) {
            for (const range of rangeResults) {
                reportContent += `\n题目范围 ${range.start_question_id} - ${range.end_question_id} 分析:\n`;
                reportContent += `得分: ${range.total_score}\n`;
                reportContent += `状态: ${range.mental_status}\n`;
                reportContent += `建议: ${range.advice}\n`;
            }
        } else {
            reportContent += `\n暂无详细分析数据\n`;
            if (mentalStatus) {
                reportContent += `\n根据总分分析，您的心理状态为: ${mentalStatus}\n`;
            }
            if (advice) {
                reportContent += `\n建议: ${advice}\n`;
            }
        }

        // 添加日志以检查发送的数据
        console.log('发送报告详情:', {
            reportId: report.id,
            hasRangeResults: !!report.range_results,
            rangeResultsCount: Array.isArray(report.range_results) ? report.range_results.length : 
                              (typeof report.range_results === 'object' && report.range_results !== null ? Object.keys(report.range_results).length : 0)
        });

        res.status(200).json({
            status: 200,
            message: '获取心理报告详情成功',
            data: {
                id: report.id,
                questionnaire_id: report.questionnaire_id,
                questionnaire_title: report.questionnaire?.title || '未知问卷',
                user_id: report.user_id,
                user_name: userName,
                report_id: report.id,
                content: reportContent,
                total_score: report.total_score,
                mental_status: mentalStatus || report.mental_status,
                advice: advice || report.advice,
                range_results: report.range_results || [],
                created_at: report.createdAt || new Date()
            }
        });
    } catch (error) {
        console.error('获取心理报告详情失败:', error);
        res.status(500).json({
            status: 500,
            message: '获取心理报告详情失败',
            error: error.message
        });
    }
};

// 创建心理报告
exports.createPsychologyReport = async (req, res) => {
    const { user_id, content } = req.body;
    try {
        const report = await PsychologyReport.create({
            user_id,
            content
        });
        res.status(201).json({
            status: 201,
            message: '心理报告创建成功',
            data: report
        });
    } catch (error) {
        console.error(error);
        res.status(500).json({
            status: 500,
            message: '创建心理报告失败'
        });
    }
};

// 更新心理报告
exports.updatePsychologyReport = async (req, res) => {
    const { id } = req.body;
    const { content } = req.body;
    try {
        const [updated] = await PsychologyReport.update({
            content
        }, {
            where: { id }
        });
        if (updated) {
            res.status(200).json({
                status: 200,
                message: '心理报告更新成功'
            });
        } else {
            res.status(404).json({
                status: 404,
                message: '未找到指定的心理报告'
            });
        }
    } catch (error) {
        console.error(error);
        res.status(500).json({
            status: 500,
            message: '更新心理报告失败'
        });
    }
};

// 删除心理报告
exports.deletePsychologyReport = async (req, res) => {
    const { id } = req.body;
    try {
        const deleted = await PsychologyReport.destroy({
            where: { id }
        });
        if (deleted) {
            res.status(200).json({
                status: 200,
                message: '心理报告删除成功'
            });
        } else {
            res.status(404).json({
                status: 404,
                message: '未找到指定的心理报告'
            });
        }
    } catch (error) {
        console.error(error);
        res.status(500).json({
            status: 500,
            message: '删除心理报告失败'
        });
    }
};    

// 批量导入题目分数段和心理状态关联
exports.ImportQuestionScoreRanges = async (req, res) => {
    try {
        if (!req.file) {
            return res.status(400).json({
                status: 400,
                message: '请上传Excel文件'
            });
        }

        const workbook = xlsx.readFile(req.file.path);
        const sheetName = workbook.SheetNames[0];
        const data = xlsx.utils.sheet_to_json(workbook.Sheets[sheetName]);

        const importedRanges = [];
        const skippedRanges = [];

        for (const row of data) {
            try {
                // 校验必填字段
                if (!row.questionnaire_id || !row.min_score || !row.max_score || 
                    !row.mental_status) {
                    skippedRanges.push({
                        row: row,
                        reason: '缺少必填字段(questionnaire_id/min_score/max_score/mental_status)'
                    });
                    continue;
                }

                // 验证问卷ID是否存在
                const questionnaire = await PsychologyQuestionnaire.findByPk(row.questionnaire_id);
                if (!questionnaire) {
                    skippedRanges.push({
                        row: row,
                        reason: `问卷ID ${row.questionnaire_id} 不存在`
                    });
                    continue;
                }

                // 创建分数段和心理状态关联
                const scoreRange = await QuestionScoreRange.create({
                    questionnaire_id: row.questionnaire_id,
                    min_score: row.min_score,
                    max_score: row.max_score,
                    mental_status: row.mental_status,
                    advice: row.advice || ''
                });

                importedRanges.push(scoreRange);
            } catch (error) {
                console.error('导入分数段失败:', error);
                skippedRanges.push({
                    row: row,
                    reason: error.message
                });
            }
        }

        res.status(200).json({
            status: 200,
            message: '题目分数段和心理状态关联导入完成',
            data: {
                imported_count: importedRanges.length,
                skipped_count: skippedRanges.length,
                imported_ranges: importedRanges.map(r => r.id),
                skipped_ranges: skippedRanges
            }
        });
    } catch (error) {
        console.error('导入失败:', error);
        res.status(500).json({
            status: 500,
            message: '导入题目分数段和心理状态关联失败',
            error: error.message
        });
    }
};

// 批量导入问卷题目
exports.ImportQuestions = async (req, res) => {
    try {
        if (!req.file) {
            return res.status(400).json({
                status: 400,
                message: '请上传Excel文件'
            });
        }

        const workbook = xlsx.readFile(req.file.path);
        const sheetName = workbook.SheetNames[0];
        const data = xlsx.utils.sheet_to_json(workbook.Sheets[sheetName]);

        const importedQuestions = [];
        const skippedQuestions = [];

        for (const row of data) {
            try {
                // 校验必填字段
                if (!row.question_text || !row.question_type) {
                    skippedQuestions.push({
                        row: row,
                        reason: '缺少必填字段(question_text或question_type)'
                    });
                    continue;
                }

                // 创建题目
                const question = await Question.create({
                    question_type: row.question_type,
                    question_text: row.question_text
                });

                // 如果有选项，创建选项
                if (row.options && Array.isArray(row.options)) {
                    for (const option of row.options) {
                        if (option.text && option.score !== undefined) {
                            await QuestionOption.create({
                                question_id: question.id,
                                option_text: option.text,
                                option_score: option.score
                            });
                        }
                    }
                }

                importedQuestions.push(question);
            } catch (error) {
                console.error('导入题目失败:', error);
                skippedQuestions.push({
                    row: row,
                    reason: error.message
                });
            }
        }

        res.status(200).json({
            status: 200,
            message: '问卷题目导入完成',
            data: {
                imported_count: importedQuestions.length,
                skipped_count: skippedQuestions.length,
                imported_questions: importedQuestions.map(q => q.id),
                skipped_questions: skippedQuestions
            }
        });
    } catch (error) {
        console.error('导入失败:', error);
        res.status(500).json({
            status: 500,
            message: '导入问卷题目失败',
            error: error.message
        });
    }
};
